package org.dealpdf.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dealpdf.model.ContainmentMeasure;
import org.dealpdf.model.ProblemMap;
import org.dealpdf.model.TemporaryMeasure;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
    public static void generate8DExcel(String templatePath, String outputPath, ProblemMap problemMap, List<ContainmentMeasure> containmentMeasures, List<TemporaryMeasure> temporaryMeasures) throws IOException {
        try (FileInputStream fis = new FileInputStream(templatePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis);
             FileOutputStream fos = new FileOutputStream(outputPath)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 替换单行字段
            replaceSingleFields(sheet, problemMap);

            // 找到“围堵措施”和“临时措施”插入点
            int containmentStartRow = findSectionRow(sheet, "围堵措施");
            int tempStartRow = findSectionRow(sheet, "临时措施");

            // 调试信息
            System.out.println("Containment start row: " + containmentStartRow);
            System.out.println("Temporary start row: " + tempStartRow);

            // 插入围堵措施
            if (containmentStartRow != -1) {
                int headerRow = containmentStartRow + 1;
                int dataStartRow = containmentStartRow + 2;
                System.out.println("Inserting containment header at row: " + headerRow);
                System.out.println("Inserting containment data starting at row: " + dataStartRow);

                // 创建围堵措施表头行（如果不存在）
                Row header = sheet.getRow(headerRow);
                if (header == null) header = sheet.createRow(headerRow);
                createContainmentHeaderRow(header);

                // 仅解除数据插入行及后续行的合并单元格（A-H 列，索引 0-7）
                unmergeCells(sheet, dataStartRow, dataStartRow + containmentMeasures.size() - 1, 0, 7);
                for (int i = 0; i < containmentMeasures.size(); i++) {
                    Row row = sheet.getRow(dataStartRow + i);
                    if (row == null) row = sheet.createRow(dataStartRow + i);
                    createContainmentMeasureRow(row, containmentMeasures.get(i));
                }
                // 调整行数以避免覆盖
                int existingRows = countExistingRows(sheet, dataStartRow);
                if (existingRows > 0) {
                    sheet.shiftRows(dataStartRow + containmentMeasures.size(), sheet.getLastRowNum(), containmentMeasures.size(), true, false);
                }
            }

            // 插入临时措施
            if (tempStartRow != -1) {
                int headerRow = tempStartRow + 1;
                int dataStartRow = tempStartRow + 2;
                System.out.println("Inserting temporary header at row: " + headerRow);
                System.out.println("Inserting temporary data starting at row: " + dataStartRow);

                // 创建临时措施表头行（如果不存在）
                Row header = sheet.getRow(headerRow);
                if (header == null) header = sheet.createRow(headerRow);
                createTemporaryHeaderRow(header);

                // 仅解除数据插入行及后续行的合并单元格（A-H 列，索引 0-7）
                unmergeCells(sheet, dataStartRow, dataStartRow + temporaryMeasures.size() - 1, 0, 7);
                for (int i = 0; i < temporaryMeasures.size(); i++) {
                    Row row = sheet.getRow(dataStartRow + i);
                    if (row == null) row = sheet.createRow(dataStartRow + i);
                    createTemporaryMeasureRow(row, temporaryMeasures.get(i));
                }
                // 调整行数以避免覆盖
                int existingRows = countExistingRows(sheet, dataStartRow);
                if (existingRows > 0) {
                    sheet.shiftRows(dataStartRow + temporaryMeasures.size(), sheet.getLastRowNum(), temporaryMeasures.size(), true, false);
                }
            }

            // 保存文件
            workbook.write(fos);
        }
    }

    private static void replaceSingleFields(Sheet sheet, ProblemMap problemMap) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue();
                    Map<String, String> data = new HashMap<>();
                    data.put("problem_title", problemMap.getProblemTitle());
                    data.put("jira_code", problemMap.getJiraCode());
                    data.put("happen_place", problemMap.getHappenPlace());
                    data.put("happen_date", problemMap.getHappenDate());
                    data.put("charge_person_name", problemMap.getChargePersonName());
                    data.put("charge_person_dept_name", problemMap.getChargePersonDeptName());
                    data.put("launch_person_name", problemMap.getLaunchPersonName());
                    data.put("launch_person_dept_name", problemMap.getLaunchPersonDeptName());
                    data.put("create_time", problemMap.getCreateTime());
                    data.put("problem_code", problemMap.getProblemCode());
                    data.put("problem_source", problemMap.getProblemSource());
                    data.put("severity", problemMap.getSeverity());
                    data.put("occurrence_frequency", problemMap.getOccurrenceFrequency());
                    data.put("vin", problemMap.getVin());
                    data.put("part_serial_number", problemMap.getPartSerialNumber());
                    data.put("software_baseline", problemMap.getSoftwareBaseline());
                    data.put("hardware_version", problemMap.getHardwareVersion());
                    data.put("instrument_fault_warning", problemMap.getInstrumentFaultWarning());
                    data.put("supplier_name", problemMap.getSupplierName());
                    data.put("project_name", problemMap.getProjectName());
                    data.put("who", problemMap.getWho());
                    data.put("what", problemMap.getWhat());
                    data.put("where_place", problemMap.getWherePlace());
                    data.put("when_time", problemMap.getWhenTime());
                    data.put("amount", problemMap.getAmount());
                    data.put("d2_problem_description", problemMap.getProblemTitle()); // 示例值
                    data.put("number", "10"); // 示例值
                    data.put("status_desc", "正常"); // 示例值
                    data.put("close_desc", "问题已解决"); // 示例值

                    for (Map.Entry<String, String> entry : data.entrySet()) {
                        String placeholder = "${problemMap." + entry.getKey() + "}";
                        if (cellValue.contains(placeholder)) {
                            cell.setCellValue(cellValue.replace(placeholder, entry.getValue()));
                        }
                    }
                }
            }
        }
    }

    private static int findSectionRow(Sheet sheet, String sectionTitle) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue().trim();
                    if (cellValue.equals(sectionTitle)) {
                        return row.getRowNum(); // 返回表头行
                    }
                }
            }
        }
        return -1; // 未找到
    }

    private static int countExistingRows(Sheet sheet, int startRow) {
        int count = 0;
        for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null && !isRowEmpty(row)) {
                count++;
            } else {
                break;
            }
        }
        return count;
    }

    private static boolean isRowEmpty(Row row) {
        if (row == null) return true;
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    private static void unmergeCells(Sheet sheet, int startRow, int endRow, int startCol, int endCol) {
        // 移除指定范围内的合并单元格，保护表头行
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstRow() > startRow && region.getLastRow() <= endRow &&
                    region.getFirstColumn() >= startCol && region.getLastColumn() <= endCol) {
                sheet.removeMergedRegion(i);
            }
        }
    }

    private static void createContainmentHeaderRow(Row row) {
        // 设置围堵措施表头（F: 实施日期, G: 取消日期, H: 执行效果确认）
        Cell cell1 = row.getCell(5);
        if (cell1 == null) cell1 = row.createCell(5);
        cell1.setCellValue("实施日期");
        Cell cell2 = row.getCell(6);
        if (cell2 == null) cell2 = row.createCell(6);
        cell2.setCellValue("取消日期");
        Cell cell3 = row.getCell(7);
        if (cell3 == null) cell3 = row.createCell(7);
        cell3.setCellValue("执行效果确认");
        // A-E 留空，模板已合并为“围堵措施描述”
    }

    private static void createTemporaryHeaderRow(Row row) {
        // 设置临时措施表头（D: 验证完成日期, E: 计划完成日期, F: 实际完成日期, G: 取消日期, H: 执行效果确认）
        Cell cell1 = row.getCell(3);
        if (cell1 == null) cell1 = row.createCell(3);
        cell1.setCellValue("验证完成日期");
        Cell cell2 = row.getCell(4);
        if (cell2 == null) cell2 = row.createCell(4);
        cell2.setCellValue("计划完成日期");
        Cell cell3 = row.getCell(5);
        if (cell3 == null) cell3 = row.createCell(5);
        cell3.setCellValue("实际完成日期");
        Cell cell4 = row.getCell(6);
        if (cell4 == null) cell4 = row.createCell(6);
        cell4.setCellValue("取消日期");
        Cell cell5 = row.getCell(7);
        if (cell5 == null) cell5 = row.createCell(7);
        cell5.setCellValue("执行效果确认");
        // A-C 留空，模板已合并为“临时措施描述”
    }

    private static void createContainmentMeasureRow(Row row, ContainmentMeasure measure) {
        // 围堵措施 A-E 填充描述，F-G-H 填充数据
        Cell cellDesc = row.getCell(0); // A 列（A-E 合并）
        if (cellDesc == null) cellDesc = row.createCell(0);
        cellDesc.setCellValue(measure.getDescription()); // 填充描述到 A-E
        Cell cell1 = row.getCell(5); // 实施日期 (F)
        if (cell1 == null) cell1 = row.createCell(5);
        cell1.setCellValue(measure.getImplementationDate());
        Cell cell2 = row.getCell(6); // 取消日期 (G)
        if (cell2 == null) cell2 = row.createCell(6);
        cell2.setCellValue(measure.getCancellationDate());
        Cell cell3 = row.getCell(7); // 执行效果确认 (H)
        if (cell3 == null) cell3 = row.createCell(7);
        cell3.setCellValue(measure.getEffectConfirmation());
    }

    private static void createTemporaryMeasureRow(Row row, TemporaryMeasure measure) {
        // 临时措施 A-C 填充描述，D-E-F-G-H 填充数据
        Cell cellDesc = row.getCell(0); // A 列（A-C 合并）
        if (cellDesc == null) cellDesc = row.createCell(0);
        cellDesc.setCellValue(measure.getDescription()); // 填充描述到 A-C
        Cell cell1 = row.getCell(3); // 验证完成日期 (D)
        if (cell1 == null) cell1 = row.createCell(3);
        cell1.setCellValue(measure.getVerificationDate());
        Cell cell2 = row.getCell(4); // 计划完成日期 (E)
        if (cell2 == null) cell2 = row.createCell(4);
        cell2.setCellValue(measure.getPlannedDate());
        Cell cell3 = row.getCell(5); // 实际完成日期 (F)
        if (cell3 == null) cell3 = row.createCell(5);
        cell3.setCellValue(measure.getActualDate());
        Cell cell4 = row.getCell(6); // 取消日期 (G)
        if (cell4 == null) cell4 = row.createCell(6);
        cell4.setCellValue(measure.getCancellationDate());
        Cell cell5 = row.getCell(7); // 执行效果确认 (H)
        if (cell5 == null) cell5 = row.createCell(7);
        cell5.setCellValue(measure.getEffectConfirmation());
    }
}